import java.sql.*;
import java.util.ArrayList;

public class PostgreSQLServer extends GeneralDatabase {

    Connection conn = null;
    Statement stmt = null;


    /**
     *
     * @param host the address of the sql server
     * @param port the listening port of the sql server
     * @param username the default username
     * @param password the relevant password
     * @param database the name of the neurosama.backend.database
     */
    public PostgreSQLServer(String host,String port,String username,String password,String database) {
        super("postgresql",host,port,username,password);
        try{
            Class.forName("org.postgresql.Driver");
        }catch(ClassNotFoundException e){
            e.printStackTrace();

        }
        try {
            String url = "jdbc:postgresql://"+host+":"+port+"/"+database;
            conn = DriverManager.getConnection(url, username, password);
            stmt = conn.createStatement();
        }catch(SQLException se){
            se.printStackTrace();
        }catch(Exception e){
            e.printStackTrace();
        }
    }






    private ResultSet executeQuery(String sql){
        ResultSet rs = null;
        try{
            rs = stmt.executeQuery(sql);
        }catch(SQLException se){
            se.printStackTrace();
        }
        return rs;
    }




    /**
     * @brief to register a new user into the system.
     * @param Username The username to be registered
     * @param Password The password to associate with the username
     * @return `String` indicating registration status:
     *         {@code "SUCCESS"} for successful registration,
     *         {@code "USER_EXISTS"} if username already taken,
     *         {@code "ERROR"} for system failures
     * @example {@code registerUser("newUser", "securePass123")}
     *
     * @implNote Performs atomic check-and-insert operation using two prepared statements:
     *           1. Verify username uniqueness
     *           2. Insert new credentials into {@code earlytest.users} table
     ***/
    public String tryRigister(String Username, String Password) throws DuplicatedItem {
        String checkSql = "SELECT username FROM earlytest.users WHERE username = ?";
        String insertSql = "INSERT INTO earlytest.users(username, password, permissions,last_login) VALUES(?, ?, ?,now())";

        try (PreparedStatement checkStmt = conn.prepareStatement(checkSql);
             PreparedStatement insertStmt = conn.prepareStatement(insertSql)) {

            // 检查用户名是否存在
            checkStmt.setString(1, Username);
            ResultSet rs = checkStmt.executeQuery();
            if (rs.next()) {
                throw new DuplicatedItem(Username);
            }

            // 插入新用户（默认普通权限）
            insertStmt.setString(1, Username);
            insertStmt.setString(2, Password);
            insertStmt.setInt(3, 755); // 默认普通权限
            int affectedRows = insertStmt.executeUpdate();
            return affectedRows > 0 ? "SUCCESS" : "ERROR";

        } catch (SQLException e) {
            e.printStackTrace();
            return "ERROR";
        }
    }





    public String tryUseradd(String queryUsername, String newUsername, String newPassword, int Permission) throws DuplicatedItem,PermissionDenied{
        String checkAdminSql = "SELECT permissions FROM earlytest.users WHERE username = ?";
        String checkExistSql = "SELECT username FROM earlytest.users WHERE username = ?";
        String insertSql = "INSERT INTO earlytest.users(username, password, permissions,modified_time,permitted_user) VALUES(?, ?, ?,now(),?)";

        try (PreparedStatement checkStmt = conn.prepareStatement(checkAdminSql);
             PreparedStatement insertStmt = conn.prepareStatement(insertSql)) {

            checkStmt.setString(1, queryUsername);
            ResultSet rs = checkStmt.executeQuery();
            if (!rs.next() || rs.getInt("permissions") != 777) {
                throw new PermissionDenied("queryUsername");
            }

            try (PreparedStatement checkUserStmt = conn.prepareStatement(checkExistSql)) {
                checkUserStmt.setString(1, newUsername);
                ResultSet userRs = checkUserStmt.executeQuery();
                if (userRs.next()) {
                    throw new DuplicatedItem(newUsername);
                }
            }

            // 插入新用户（默认普通权限）
            insertStmt.setString(1, newUsername);
            insertStmt.setString(2, newPassword);
            insertStmt.setInt(3, Permission); // 默认普通权限
            insertStmt.setString(4, queryUsername);
            int affectedRows = insertStmt.executeUpdate();
            return affectedRows > 0 ? "SUCCESS" : "ERROR";

        } catch (SQLException e) {
            e.printStackTrace();
            return "ERROR";
        }
    }



    public String tryUserdel(String adminUsername, String targetUsername) throws PermissionDenied, EmptyOutput {
        String checkAdminSql = "SELECT permissions FROM earlytest.users WHERE username = ?";
        String checkUserSql = "SELECT username FROM earlytest.users WHERE username = ?";
        String deleteSql = "UPDATE earlytest.users SET modified_time=now(), permitted_user=?, is_deleted='true' WHERE username = ?\n";
        try (PreparedStatement checkStmt = conn.prepareStatement(checkAdminSql)) {
            checkStmt.setString(1, adminUsername);
            ResultSet rs = checkStmt.executeQuery();
            if (!rs.next() || rs.getInt("permissions") != 777) {
                throw new PermissionDenied("adminUsername");
            }
        } catch (SQLException e) {
            e.printStackTrace();
            return "ERROR";
        }


        try (PreparedStatement checkUserStmt = conn.prepareStatement(checkUserSql)) {
            checkUserStmt.setString(1, targetUsername);
            ResultSet userRs = checkUserStmt.executeQuery();
            if (!userRs.next()) {
                throw new EmptyOutput("deleteUser");
            }
        } catch (SQLException e) {
            e.printStackTrace();
            return "ERROR";
        }

        try (PreparedStatement deleteStmt = conn.prepareStatement(deleteSql)) {
            deleteStmt.setString(2, adminUsername);
            deleteStmt.setString(1, targetUsername);
            int affectedRows = deleteStmt.executeUpdate();
            return affectedRows > 0 ? "SUCCESS" : "ERROR";
        } catch (SQLException e) {
            e.printStackTrace();
            return "ERROR";
        }
    }


private void deleteMatchingSymptoms(DiseaseItem item) throws SQLException {
        //UPDATE earlytest.users SET modified_time=now(), permitted_user=?, is_deleted='false' WHERE username = ?
    String sql = "UPDATE symptoms SET modified_time=now(), permitted_user=?, is_deleted='true' WHERE secondarysymptoms = ? AND impact = ? AND reliefmethods = ?";
    try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
        pstmt.setString(1, item.Symptom());
        pstmt.setString(2, item.Impact());
        pstmt.setString(3, item.Reliefmethod());
        pstmt.executeUpdate();
    }
}
    private void deleteMatchingDiseases(DiseaseItem item) throws SQLException {
        String sql = "UPDATE Diseases SET modified_time=now(), permitted_user=?, is_deleted='true' WHERE cause = ? AND diseasetype = ? AND urgency = ?";
        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, item.Cause());
            pstmt.setString(2, item.Diseasetype());
            pstmt.setString(3, item.Urgency());
            pstmt.executeUpdate();
        }
    }
    private void deleteMatchingTreatments(DiseaseItem item) throws SQLException {
        String sql = "UPDATE Treatments SET modified_time=now(), permitted_user=?, is_deleted='true' WHERE medication = ? AND diet = ? AND department = ?";
        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, item.Medication());
            pstmt.setString(2, item.Diet());
            pstmt.setString(3, item.Department());
            pstmt.executeUpdate();
        }
    }
    private void deleteMatchingSymptomDiseaseRelations(DiseaseItem item) throws SQLException {
        String sql = "UPDATE SymptomDiseaseRelation SET is_deleted='true' WHERE symptomid IN ("
                + "SELECT symptomid FROM Symptoms "
                + "WHERE secondarysymptoms = ? AND impact = ? AND reliefmethods = ?)"
                + "AND diseaseid IN ("
                + "SELECT diseaseid FROM Diseases "
                + "WHERE cause = ? AND diseasetype = ? AND urgency = ?)";
        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, item.Symptom());
            pstmt.setString(2, item.Impact());
            pstmt.setString(3, item.Reliefmethod());
            pstmt.setString(4, item.Cause());
            pstmt.setString(5, item.Diseasetype());
            pstmt.setString(6, item.Urgency());
            pstmt.executeUpdate();
        }
    }
    private void deleteMatchingDiseaseTreatmentRelations(DiseaseItem item) throws SQLException {
        String sql = "DELETE FROM DiseaseTreatmentRelation WHERE diseaseid IN ("
                + "SELECT diseaseid FROM Diseases "
                + "WHERE cause = ? AND diseasetype = ? AND urgency = ?)"
                + "AND treatmentid IN ("
                + "SELECT treatmentid FROM Treatments "
                + "WHERE medication = ? AND diet = ? AND department = ?)";
        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, item.Cause());
            pstmt.setString(2, item.Diseasetype());
            pstmt.setString(3, item.Urgency());
            pstmt.setString(4, item.Medication());
            pstmt.setString(5, item.Diet());
            pstmt.setString(6, item.Department());
            pstmt.executeUpdate();
        }
    }
    private void checkAdminPermission(String username) throws PermissionDenied {
        // 示例：检查用户是否为管理员
        String sql = "SELECT permissions FROM earlytest.users WHERE username = ?";
        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, username);
            ResultSet rs = pstmt.executeQuery();
            if (rs.next() && !rs.getString("permissions").equals(String.valueOf(777))) {
                throw new PermissionDenied(username+"用户无权执行此操作");
            }
        }catch(SQLException se){
            se.printStackTrace();
        }
    }
    public String tryItemdel(String queryUsername, DiseaseItem item) throws PermissionDenied {
        if (item == null || item.isEmpty()) {
            return "EMPTY_INPUT";
        }

        // 权限验证
        checkAdminPermission(queryUsername);

        try {
            conn.setAutoCommit(false); // 开启事务

            // 删除症状-疾病关联
            deleteMatchingSymptomDiseaseRelations(item);

            // 删除疾病-诊疗方式关联
            deleteMatchingDiseaseTreatmentRelations(item);

            // 删除诊疗方式表
            deleteMatchingTreatments(item);

            // 删除疾病表
            deleteMatchingDiseases(item);

            // 删除症状表
            deleteMatchingSymptoms(item);

            conn.commit(); // 提交事务
            return "SUCCESS";

        } catch (SQLException e) {
            try {
                if (conn != null) {
                    conn.rollback(); // 回滚
                }
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            e.printStackTrace();
            return "ERROR";
        } finally {
            try {
                if (conn != null) {
                    conn.setAutoCommit(true);
                }
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        }
    }

    /**
     * @brief Modify the permissions of a specified user
     * @param adminUsername Administrator username (must have 777 permission)
     * @param targetUsername Target username to modify
     * @param newPermission New permission value
     * @return Operation result: "SUCCESS", "PERMISSION_DENIED", "ERROR", or "USER_NOT_FOUND"
     */

    public String tryUsermod(String adminUsername, String targetUsername, int newPermission) throws PermissionDenied, EmptyOutput {
        String checkAdminSql = "SELECT permissions FROM earlytest.users WHERE username = ?";
        String checkUserSql = "SELECT username FROM earlytest.users WHERE username = ?";
        String updatePermissionSql = "UPDATE earlytest.users SET permissions = ? WHERE username = ?";

        try (PreparedStatement checkAdminStmt = conn.prepareStatement(checkAdminSql)) {
            checkAdminStmt.setString(1, adminUsername);
            ResultSet rs = checkAdminStmt.executeQuery();
            if (!rs.next() || rs.getInt("permissions") != 777) {
                throw new PermissionDenied(adminUsername);
            }
        } catch (SQLException e) {
            e.printStackTrace();
            return "ERROR";
        }

        try (PreparedStatement checkUserStmt = conn.prepareStatement(checkUserSql)) {
            checkUserStmt.setString(1, targetUsername);
            ResultSet userRs = checkUserStmt.executeQuery();
            if (!userRs.next()) {
                throw new EmptyOutput(targetUsername);
            }
        } catch (SQLException e) {
            e.printStackTrace();
            return "ERROR";
        }

        try (PreparedStatement updateStmt = conn.prepareStatement(updatePermissionSql)) {
            updateStmt.setInt(1, newPermission);
            updateStmt.setString(2, targetUsername);
            int affectedRows = updateStmt.executeUpdate();
            return affectedRows > 0 ? "SUCCESS" : "ERROR";
        } catch (SQLException e) {
            e.printStackTrace();
            return "ERROR";
        }
    }





    /**
     * @brief to detect if a user could log in successfully.
     * @param username The username that you want to search for
     * @param password The password that you want to search for
     * @return `String` of some format value, which indicates the user's permission
     *          invalid output would be {@code "ERROR"}, empty output would be {@code "NULL"}
     * @example {@code  tryLogin("username","password");}
     *
     ***/
    public String tryLogin(String username, String password) throws EmptyOutput,PermissionDenied{
        String sql = "SELECT permissions FROM earlytest.users WHERE username = ? AND password = ?";
        String updatesql = "UPDATE earlytest.users SET last_login = now() WHERE username = ?";

        try{
            PreparedStatement pstmt = conn.prepareStatement(sql);
            PreparedStatement update = conn.prepareStatement(updatesql);
            pstmt.setString(1, username);
            pstmt.setString(2, password);
            update.setString(1, username);
            ResultSet rs = pstmt.executeQuery();

            if (rs.next()) {
                int perm = rs.getInt("permissions");
                if (perm == 755 || perm == 777) { // 显式检查有效权限
                    return String.valueOf(perm);
                }
                else{
                    throw new PermissionDenied("username");
                }
            }
            else{
                throw new EmptyOutput("tryLogin");
            }
        } catch (SQLException se) {
            se.printStackTrace();
            return "ERROR";
        }
    }


    /**
     * @brief to Search for the {@code Cause} {@code Impact} {@code Treatments}
     * @param Symptom The symptom that you want to search for
     * @return `String` : a combination of its description
     * @example {@code println(querySymptom("胸痛"))}
     *
     ***/
    public DiseaseItem[] querySymptoms(String Symptom) throws EmptyOutput
    {
        if(Symptom==null|| Symptom.isEmpty()){
            //throw new EmptyInput();
        }
        Symptom = "%" + Symptom + "%";
        ResultSet rs = null;
        String psql =
                """
                SELECT s.*, d.*
                FROM symptoms s
                JOIN symptomdiseaserelation sdr ON s.symptomid = sdr.symptomid
                JOIN diseases d ON sdr.diseaseid = d.diseaseid
                WHERE s.secondarysymptoms LIKE ?;
                """;
        ArrayList<DiseaseItem> temp = new ArrayList<>();
        try {
            PreparedStatement pstmt = conn.prepareStatement(psql);
            pstmt.setString(1, Symptom);
            //pstmt.
            rs = pstmt.executeQuery();
            while (rs.next()) {
                DiseaseItem it = new DiseaseItem();
                it.Symptom(rs.getString("secondarysymptoms"));
                it.Cause(rs.getString("cause"));
                it.Reliefmethod(rs.getString("reliefmethods"));
                it.Impact(rs.getString("impact"));
                it.Organ(rs.getString("organ"));
                it.Diseasetype(rs.getString("diseasetype"));
                temp.add(it);
            }
        } catch (SQLException se) {
            se.printStackTrace();
        }
        if (rs != null) {
            DiseaseItem[] result = new DiseaseItem[temp.size()];
            return temp.toArray(result);
        } else {
            throw new EmptyOutput("Symptom: "+Symptom);
        }
    }


    public DiseaseItem[] queryDiseases(String Disease) throws EmptyInput,EmptyOutput
    {
        if(Disease==null|| Disease.isEmpty()){
            //throw new EmptyInput();
        }
        Disease = "%" + Disease + "%";
        ResultSet rs = null;
        String psql =
                """
                        SELECT s.*, d.*,t.*
                        FROM symptoms s
                        JOIN symptomdiseaserelation sdr ON s.symptomid = sdr.symptomid
                        JOIN diseases d ON sdr.diseaseid = d.diseaseid
                		JOIN DiseaseTreatmentRelation dtr ON d.diseaseid = dtr.diseaseid
                        JOIN Treatments t ON dtr.treatmentid = t.treatmentid
                        WHERE d.cause LIKE ?;
                """;
        ArrayList<DiseaseItem> temp = new ArrayList<>();
        try {
            PreparedStatement pstmt = conn.prepareStatement(psql);
            pstmt.setString(1, Disease);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                DiseaseItem it = new DiseaseItem();
                it.Disease(rs.getString("cause"));
                it.Symptom(rs.getString("secondarysymptoms"));
                it.Diseasetype(rs.getString("diseasetype"));
                it.Medication(rs.getString("medication"));
                        it.Organ(rs.getString("organ"));
                        it.Reliefmethod(rs.getString("reliefmethods"));
                        it.Diet(rs.getString("diet"));
                        it.Impact(rs.getString("impact"));
                        it.Urgency(rs.getString("urgency"));
                        it.Department(rs.getString("department"));
                temp.add(it);
            }
        } catch (SQLException se) {
            se.printStackTrace();
        }
        if (rs != null) {
            DiseaseItem[] result = new DiseaseItem[temp.size()];
            return temp.toArray(result);
        } else {
            throw new EmptyOutput("Disease");
        }
    }

    public DiseaseItem[] queryDepartments(String Department) throws EmptyInput,EmptyOutput {
        if(Department == null || Department.isEmpty()){
            //throw new EmptyInput();
        }
        StringBuilder sb = new StringBuilder();
        Department = "%" + Department + "%";
        ResultSet rs = null;
        String psql =
                """
                        SELECT s.*, d.*,t.*
                        FROM symptoms s
                        JOIN symptomdiseaserelation sdr ON s.symptomid = sdr.symptomid
                        JOIN diseases d ON sdr.diseaseid = d.diseaseid
                		JOIN DiseaseTreatmentRelation dtr ON d.diseaseid = dtr.diseaseid
                        JOIN Treatments t ON dtr.treatmentid = t.treatmentid
                        WHERE t.department LIKE ?;
                """;
        ArrayList<DiseaseItem> temp = new ArrayList<>();
        try {
            PreparedStatement pstmt = conn.prepareStatement(psql);
            pstmt.setString(1, Department);
            //pstmt.
            rs = pstmt.executeQuery();
            while (rs.next()) {
                DiseaseItem it = new DiseaseItem();
                it.Department(rs.getString("department"));
                it.Cause(rs.getString("cause"));
                it.Symptom(rs.getString("secondarysymptoms"));
                it.Medication(rs.getString("medication"));
                it.Diet(rs.getString("diet"));
                it.Reliefmethod(rs.getString("reliefmethods"));

                temp.add(it);
            }
            } catch (SQLException se) {
                se.printStackTrace();
            }
            if (rs != null) {
                DiseaseItem[] result = new DiseaseItem[temp.size()];
                return temp.toArray(result);
            } else {
                throw new EmptyOutput("Department");
            }
    }


    //Notice that this insert MAY USE MULTIPLE METHODS to IMPLEMENT
    private int getOrCreateSymptom(Connection conn, String secondarysymptoms, String impact, String reliefmethods, boolean isDeleted,String queryUser) throws SQLException {
        String sql = "SELECT symptomid FROM Symptoms WHERE secondarysymptoms = ? AND impact = ?";
        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, secondarysymptoms);
            pstmt.setString(2, impact);
            ResultSet rs = pstmt.executeQuery();
            if (rs.next()) {
                return rs.getInt("symptomid");
            }
        }

        sql = "INSERT INTO Symptoms (organ, secondarysymptoms, impact, reliefmethods, is_deleted,modified_time,permitted_user) VALUES (?, ?, ?, ?, 'false',now(),?) RETURNING symptomid";
        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, "Unknown"); // organ 默认值
            pstmt.setString(2, secondarysymptoms);
            pstmt.setString(3, impact);
            pstmt.setString(4, reliefmethods);
            pstmt.setString(5, queryUser);
            ResultSet rs = pstmt.executeQuery();
            if (rs.next()) {
                return rs.getInt("symptomid");
            }
        }
        throw new SQLException("Failed to create symptom.");
    }
    private int getOrCreateDisease(Connection conn, String cause, String diseasetype, String urgency, boolean isDeleted,String queryUser) throws SQLException {
        String sql = "SELECT diseaseid FROM Diseases WHERE cause = ? AND diseasetype = ?";
        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, cause);
            pstmt.setString(2, diseasetype);
            ResultSet rs = pstmt.executeQuery();
            if (rs.next()) {
                return rs.getInt("diseaseid");
            }
        }

        sql = "INSERT INTO Diseases (cause, diseasetype, urgency, is_deleted,modified_time,permitted_user) VALUES (?, ?, ?, 'false',now(),?) RETURNING diseaseid";
        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, cause);
            pstmt.setString(2, diseasetype);
            pstmt.setString(3, urgency);
            pstmt.setString(4, queryUser);
            ResultSet rs = pstmt.executeQuery();
            if (rs.next()) {
                return rs.getInt("diseaseid");
            }
        }
        throw new SQLException("Failed to create disease.");
    }
    private int getOrCreateTreatment(Connection conn, String medication, String diet, String department, boolean isDeleted,String queryUser) throws SQLException {
        String sql = "SELECT treatmentid FROM Treatments WHERE medication = ? AND department = ?";
        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, medication);
            pstmt.setString(2, department);
            ResultSet rs = pstmt.executeQuery();
            if (rs.next()) {
                return rs.getInt("treatmentid");
            }
        }

        sql = "INSERT INTO Treatments (medication, diet, department, is_deleted,modified_time,permitted_user) VALUES (?, ?, ?, 'false',now(),?) RETURNING treatmentid";
        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, medication);
            pstmt.setString(2, diet);
            pstmt.setString(3, department);
            pstmt.setString(4,queryUser);
            ResultSet rs = pstmt.executeQuery();
            if (rs.next()) {
                return rs.getInt("treatmentid");
            }
        }
        throw new SQLException("Failed to create treatment.");
    }
    public String tryItemadd(String queryUsername, DiseaseItem item) throws PermissionDenied {
        if (item == null || item.isEmpty()) {
            return null;
        }

        boolean isDeleted = item.Test(); // 获取是否是测试数据

        try {
            conn.setAutoCommit(false); // 开启事务

            int symptomId = getOrCreateSymptom(conn, item.Symptom(), item.Impact(), item.Reliefmethod(), isDeleted,queryUsername);
            int diseaseId = getOrCreateDisease(conn, item.Cause(), item.Diseasetype(), item.Urgency(), isDeleted,queryUsername);
            int treatmentId = getOrCreateTreatment(conn, item.Medication(), item.Diet(), item.Department(), isDeleted,queryUsername);

            linkSymptomDisease(conn, symptomId, diseaseId,isDeleted);
            linkDiseaseTreatment(conn, diseaseId, treatmentId,isDeleted);

            conn.commit(); // 提交事务
            conn.setAutoCommit(true);
            return "SUCCESS";

        } catch (SQLException | EmptyOutput e) {
            if (conn != null) {
                try {
                    conn.rollback(); // 回滚
                } catch (SQLException ex) {
                    ex.printStackTrace();
                }
            }
            e.printStackTrace();
            return null;
        }finally {
            try {
                if (conn != null) {
                    conn.setAutoCommit(true);
                }
            }catch (SQLException ex) {
                ex.printStackTrace();
            }

        }
    }
    private void linkSymptomDisease(Connection conn, int symptomId, int diseaseId, boolean isDeleted) throws SQLException {
        String sql = "INSERT INTO SymptomDiseaseRelation (symptomid, diseaseid, is_deleted) VALUES (?, ?, ?) ON CONFLICT DO NOTHING";
        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setInt(1, symptomId);
            pstmt.setInt(2, diseaseId);
            pstmt.setBoolean(3, isDeleted);
            pstmt.executeUpdate();
        }
    }
    private void linkDiseaseTreatment(Connection conn, int diseaseId, int treatmentId, boolean isDeleted) throws SQLException {
        String sql = "INSERT INTO DiseaseTreatmentRelation (diseaseid, treatmentid, is_deleted) VALUES (?, ?, ?) ON CONFLICT DO NOTHING";
        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setInt(1, diseaseId);
            pstmt.setInt(2, treatmentId);
            pstmt.setBoolean(3, isDeleted);
            pstmt.executeUpdate();
        }
    }


    private int getSymptomId(String secondarysymptoms, String impact, String reliefmethods)
            throws SQLException, EmptyOutput {
        String sql = "SELECT symptomid FROM Symptoms WHERE secondarysymptoms = ? AND impact = ?";
        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, secondarysymptoms);
            pstmt.setString(2, impact);
            ResultSet rs = pstmt.executeQuery();
            if (rs.next()) {
                return rs.getInt("symptomid");
            } else {
                throw new EmptyOutput("Symptom not found");
            }
        }
    }

    private int getDiseaseId(String cause, String diseasetype, String urgency)
            throws SQLException, EmptyOutput {
        String sql = "SELECT diseaseid FROM Diseases WHERE cause = ? AND diseasetype = ?";
        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, cause);
            pstmt.setString(2, diseasetype);
            ResultSet rs = pstmt.executeQuery();
            if (rs.next()) {
                return rs.getInt("diseaseid");
            } else {
                throw new EmptyOutput("Disease not found");
            }
        }
    }

    private int getTreatmentId(String medication, String diet, String department)
            throws SQLException, EmptyOutput {
        String sql = "SELECT treatmentid FROM Treatments WHERE medication = ? AND department = ?";
        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, medication);
            pstmt.setString(2, department);
            ResultSet rs = pstmt.executeQuery();
            if (rs.next()) {
                return rs.getInt("treatmentid");
            } else {
                throw new EmptyOutput("Treatment not found");
            }
        }
    }
    private void deleteSymptomDisease(Connection conn, int symptomId, int diseaseId) throws SQLException {
        String sql = "DELETE FROM SymptomDiseaseRelation WHERE symptomid = ? AND diseaseid = ?";
        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setInt(1, symptomId);
            pstmt.setInt(2, diseaseId);
            pstmt.executeUpdate();
        }
    }

    private void deleteDiseaseTreatment(Connection conn, int diseaseId, int treatmentId) throws SQLException {
        String sql = "DELETE FROM DiseaseTreatmentRelation WHERE diseaseid = ? AND treatmentid = ?";
        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setInt(1, diseaseId);
            pstmt.setInt(2, treatmentId);
            pstmt.executeUpdate();
        }
    }
    private void deleteSymptom(Connection conn, int symptomId) throws SQLException {
        String sql = "DELETE FROM Symptoms WHERE symptomid = ?";
        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setInt(1, symptomId);
            pstmt.executeUpdate();
        }
    }

    private void deleteDisease(Connection conn, int diseaseId) throws SQLException {
        String sql = "DELETE FROM Diseases WHERE diseaseid = ?";
        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setInt(1, diseaseId);
            pstmt.executeUpdate();
        }
    }

    private void deleteTreatment(Connection conn, int treatmentId) throws SQLException {
        String sql = "DELETE FROM Treatments WHERE treatmentid = ?";
        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setInt(1, treatmentId);
            pstmt.executeUpdate();
        }
    }



    public String tryItemmod(String queryUsername, DiseaseItem item) throws PermissionDenied {
        if (item == null) {
            return "EMPTY_INPUT";
        }

        Connection conn = null;
        try {
            conn = this.conn;
            conn.setAutoCommit(false); // 开启事务

            // 权限检查
            checkAdminPermission(conn, queryUsername);


            int diseaseId = getDiseaseId(item.Cause(), item.Diseasetype(), item.Urgency());
            int symptomId = getSymptomId(item.Symptom(), item.Impact(), item.Reliefmethod());
            int treatmentId = getTreatmentId(item.Medication(), item.Diet(), item.Department());


            // 更新各个字段
            updateIfNotEmpty(conn, symptomId, "Symptoms", "secondarysymptoms", item.Symptom(),queryUsername);
            updateIfNotEmpty(conn, symptomId, "Symptoms", "impact", item.Impact(),queryUsername);
            updateIfNotEmpty(conn, symptomId, "Symptoms", "reliefmethods", item.Reliefmethod(),queryUsername);

            updateIfNotEmpty(conn, diseaseId, "Diseases", "cause", item.Cause(),queryUsername);
            updateIfNotEmpty(conn, diseaseId, "Diseases", "diseasetype", item.Diseasetype(),queryUsername);
            updateIfNotEmpty(conn, diseaseId, "Diseases", "urgency", item.Urgency(),queryUsername);

            updateIfNotEmpty(conn, treatmentId, "Treatments", "medication", item.Medication(),queryUsername);
            updateIfNotEmpty(conn, treatmentId, "Treatments", "diet", item.Diet(),queryUsername);
            updateIfNotEmpty(conn, treatmentId, "Treatments", "department", item.Department(),queryUsername);

            conn.commit(); // 提交事务
            return "SUCCESS";

        } catch (SQLException | EmptyOutput e) {
            rollbackTransaction(conn); // 回滚事务
            e.printStackTrace();
            return "ERROR";
        } finally {
            setAutoCommitTrue(conn); // 恢复自动提交
        }
    }

    private void checkAdminPermission(Connection conn, String username) throws SQLException, PermissionDenied {
        String sql = "SELECT permissions FROM earlytest.users WHERE username = ?";
        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, username);
            ResultSet rs = pstmt.executeQuery();
            if (!rs.next() || rs.getInt("permissions") != 777) {
                throw new PermissionDenied(username);
            }
        }
    }
    private int getDiseaseId(Connection conn, DiseaseItem item) throws SQLException, EmptyOutput {
        String cause = item.Cause();
        String diseasetype = item.Diseasetype();
        if (cause == null || diseasetype == null) {
            throw new EmptyOutput("Missing required fields for disease");
        }
        String sql = "SELECT diseaseid FROM Diseases WHERE cause = ? AND diseasetype = ?";
        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, cause);
            pstmt.setString(2, diseasetype);
            ResultSet rs = pstmt.executeQuery();
            if (rs.next()) {
                return rs.getInt("diseaseid");
            } else {
                throw new EmptyOutput("Disease not found");
            }
        }
    }
    private void updateIfNotEmpty(Connection conn, int id, String table, String column, Object value,String queryUser) throws SQLException {
        if (value == null || value.toString().trim().isEmpty()) {
            return;
        }
        String sql = "UPDATE " + table + " SET " + column + " = ?,"+ queryUser + " =?,modified_time = now() WHERE " + getIdColumn(table) + " = ?";
        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setObject(1, value);
            pstmt.setInt(2, id);
            pstmt.executeUpdate();
        }
    }

    private String getIdColumn(String table) {
        switch (table) {
            case "Symptoms": return "symptomid";
            case "Diseases": return "diseaseid";
            case "Treatments": return "treatmentid";
            default: throw new IllegalArgumentException("Unknown table: " + table);
        }
    }
    private void rollbackTransaction(Connection conn) {
        if (conn != null) {
            try {
                conn.rollback();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        }
    }

    private void setAutoCommitTrue(Connection conn) {
        if (conn != null) {
            try {
                conn.setAutoCommit(true);
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        }
    }


    /**
     * @apiNote MUST CALL THIS BEFORE PROGRAM's END,
     * or it will crush.
     */
    public void disconnect(){
        try{
            stmt.close();
            conn.close();
        }catch(SQLException se){
            se.printStackTrace();
        }finally{
            try {
                if(stmt!=null) stmt.close();
            } catch(SQLException se2) {
            }
            try {
                if(conn!=null) conn.close();
            } catch(SQLException se3) {
                se3.printStackTrace();
            }
        }
    }

    public void DestroyEveryTestData(String DoYouConfirm){
        if(!DoYouConfirm.equals("I Confirm To Delete them")){
            throw new RuntimeException("Your Program just Deleted All Test Data, And you didn't Confirm it");
        }
        else{
                String sql = """
                        -- 删除关联表中涉及测试疾病的记录
                        DELETE FROM SymptomDiseaseRelation
                        WHERE diseaseid IN (
                            SELECT diseaseid FROM Diseases WHERE is_deleted = TRUE
                        );
                        
                        DELETE FROM DiseaseTreatmentRelation
                        WHERE diseaseid IN (
                            SELECT diseaseid FROM Diseases WHERE is_deleted = TRUE
                        );
                        
                        -- 删除主表中的测试数据
                        DELETE FROM Symptoms WHERE is_test = TRUE;
                        DELETE FROM Diseases WHERE is_test = TRUE;
                        DELETE FROM Treatments WHERE is_test = TRUE;
                        
                        -- 可选：清除日志或其他表中与测试相关的记录...
                        """;
                try{
                    this.executeQuery(sql);
                }
                catch (Exception e) {
                    throw new RuntimeException(e);
                }
        }
    }

    public String[] getAllDepartments()throws EmptyOutput{
        ResultSet rs = null;
        String psql =
                """
                        SELECT MIN(treatmentid) AS treatmentid, department
                        FROM public.treatments
                        GROUP BY department;
                """;
        ArrayList<String> temp = new ArrayList<>();
        try {
            PreparedStatement pstmt = conn.prepareStatement(psql);
            //pstmt.
            rs = pstmt.executeQuery();
            while (rs.next()) {
                String it = rs.getString("department");
                temp.add(it);
            }
        } catch (SQLException se) {
            se.printStackTrace();
        }
        if (rs != null) {
            String[] result = new String[temp.size()];
            return temp.toArray(result);
        } else {
            throw new EmptyOutput("Department");
        }
    }

    public String[][] getAllUserinfos(){
        ResultSet rs = null;
        String psql =
                """
                        SELECT * FROM earlytest.users;
                """;
        ArrayList<String[]> temp = new ArrayList<>();
        try {
            PreparedStatement pstmt = conn.prepareStatement(psql);
            //pstmt.
            rs = pstmt.executeQuery();
            while (rs.next()) {
                String[] it = {rs.getString("username"), rs.getString("password"), rs.getString("permissions")};
                temp.add(it);
            }
        } catch (SQLException se) {
            se.printStackTrace();
        }
        if (rs != null) {
            String[][] result = new String[temp.size()][3];
            return temp.toArray(result);
        } else {
            throw new EmptyOutput("get ALL userINFOs");
        }
    }

    public String[] getAllUsers()throws EmptyOutput{
        ResultSet rs = null;
        String psql =
                """
                        SELECT username FROM earlytest.users;
                """;
        ArrayList<String> temp = new ArrayList<>();
        try {
            PreparedStatement pstmt = conn.prepareStatement(psql);
            //pstmt.
            rs = pstmt.executeQuery();
            while (rs.next()) {
                String it = rs.getString("username");
                temp.add(it);
            }
        } catch (SQLException se) {
            se.printStackTrace();
        }
        if (rs != null) {
            String[] result = new String[temp.size()];
            return temp.toArray(result);
        } else {
            throw new EmptyOutput("ALL usernames");
        }
    }

    public String checkUserinfo(String queryUser, String destUser){
        ResultSet rs = null;
        String psql =
                """
                        SELECT username FROM earlytest.users
                        WHERE username = ?;
                """;
        //username = "%"+username+"%";
        String result = null;
        try {
            PreparedStatement pstmt = conn.prepareStatement(psql);
            //pstmt.
            pstmt.setString(1, destUser);
            rs = pstmt.executeQuery();
            if (rs.next()) {
                result = (rs.getString("username"))+","+(rs.getString("password"))+","+(rs.getString("permissions"));
            }
        } catch (SQLException se) {
            se.printStackTrace();
        }
        if (result != null) {
            return result;
        } else {
            throw new EmptyOutput("specify userinfo of"+destUser+" ");
        }
    }

    public String[] queryUsers(String username)throws EmptyOutput{
        ResultSet rs = null;
        String psql =
                """
                        SELECT username FROM earlytest.users
                        WHERE username like ?;
                """;
        username = "%"+username+"%";
        ArrayList<String> temp = new ArrayList<>();
        try {
            PreparedStatement pstmt = conn.prepareStatement(psql);
            //pstmt.
            pstmt.setString(1, username);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                String it = (rs.getString("username"));
                temp.add(it);
            }
        } catch (SQLException se) {
            se.printStackTrace();
        }
        if (rs != null) {
            String[] result = new String[temp.size()];
            return temp.toArray(result);
        } else {
            throw new EmptyOutput("query user:"+username+" ");
        }
    }

}
